﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Common;
using System.Data;
using System.Configuration;
using System.Reflection;
using System.Data.SqlClient;
using System.IO;
using System.Text.RegularExpressions;
using System.Diagnostics;
using System.Dynamic;

namespace TData
{
    public class TDataBase
    {
        enum TDBType
        {
            SQLSERVER,
            ORACLE
        }

        public int CommandTimeout
        {
            get { return Command.CommandTimeout; }
            set { Command.CommandTimeout = value; }
        }

        private DbProviderFactory _factory;
        public DbProviderFactory Factory
        {
            get { return _factory; }
        }

        private DbConnection _connection;
        public DbConnection Connection
        {
            get { return _connection; }
        }

        private DbCommand _command;
        public DbCommand Command
        {
            get { return _command; }
        }

        private string _dbProviderName;
        public string DbProviderName
        {
            get { return _dbProviderName; }
        }

        private readonly string parameterNamePrefix = "@";

        private readonly TDBType tdbType;

        /// <summary>
        /// 從App.config裡的connectionStrings建立TDataBase
        /// </summary>
        /// <param name="connectionStringName">name of connectionString</param>
        public TDataBase(string connectionStringName)
        {
            // Get the collection of connection strings.
            ConnectionStringSettingsCollection settings = ConfigurationManager.ConnectionStrings;

            if (settings == null) throw new TDataBaseException("No ConnectionStringSettings in application configuration");

            try
            {
                foreach (ConnectionStringSettings cs in settings)
                {
                    if (cs.Name == connectionStringName)
                    {
                        _dbProviderName = cs.ProviderName;
                        _factory = DbProviderFactories.GetFactory(_dbProviderName);
                        _connection = Factory.CreateConnection();
                        Connection.ConnectionString = cs.ConnectionString;
                        _command = Factory.CreateCommand();
                        Command.Connection = Connection;

                        if (this.Factory is SqlClientFactory)
                        {
                            parameterNamePrefix = "@";
                            tdbType = TDBType.SQLSERVER;
                        }
                        else if (this.Factory.GetType().FullName == "System.Data.OracleClient.OracleClientFactory")
                        {
                            parameterNamePrefix = ":";
                            tdbType = TDBType.ORACLE;
                        }
                        break;
                    }
                }

                if (Connection == null)
                    throw new TDataBaseException(string.Format("Cannot find connectionString name: [{0}] in application configuration", connectionStringName));
            }
            catch (System.ArgumentException ex)
            {
                throw new TDataBaseException(string.Format("connectionString: [{0}] is not vaild", connectionStringName), ex);
            }
        }

        public bool TestConnection()
        {
            bool success = false;
            try
            {
                Connection.Open();
                if (Connection.State == ConnectionState.Open)
                {
                    success = true;
                }
                else
                {
                    success = false;
                }

            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
            return success;
        }

        #region Query

        public DataTable ExecuteDataTable(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            DataTable dtt = new DataTable();
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;

                CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                var daa = Factory.CreateDataAdapter();
                daa.SelectCommand = Command;

                dtt = new DataTable();
                daa.Fill(dtt);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return dtt;
        }

        public int ExecuteNonQuery(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            int result = 0;
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                result = Command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return result;
        }

        public object ExecuteScalar(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            object result;
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                result = Command.ExecuteScalar();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return result;
        }

        public T ExecuteScalar<T>(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                object value = Command.ExecuteScalar();

                if (value != null && value != DBNull.Value)
                {
                    if (typeof(T).IsEnum)
                    {
                        return (T)Enum.ToObject(typeof(T), Convert.ChangeType(value, typeof(T).GetEnumUnderlyingType()));
                    }
                    else
                    {
                        return (T)Convert.ChangeType(value, typeof(T));
                    }
                }
            }
            catch (System.InvalidCastException ex)
            {
                throw new TDataBaseException("TDataBase.ExecuteScalar<T> changetype failed: " + ex.Message, ex);
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return default(T);
        }

        public void ExecuteReader(string sql, Action<IDataReader> block, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Command.CommandType = commandType;
                Command.CommandText = sql;
                CreateDbParameters(paramObj);

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                using (IDataReader reader = Command.ExecuteReader())
                {
                    block(reader);
                }
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public T ExecuteOne<T>(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Type mapType = typeof(T);
                T mapInstance = default(T);

                Command.CommandType = commandType;
                Command.CommandText = sql;

                ExecuteReader(sql, (reader) =>
                {
                    /*取出sql query內的欄位名稱*/
                    List<string> columnNamesInQuery = new List<string>();
                    foreach (DataRow dr in reader.GetSchemaTable().Rows) columnNamesInQuery.Add(Convert.ToString(dr[0]));

                    while (reader.Read())
                    {
                        mapInstance = CreateTObject<T>(mapType, reader, columnNamesInQuery);
                        break;
                    }
                }, paramObj);

                return mapInstance;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public List<T> ExecuteList<T>(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                Type mapType = typeof(T);
                List<T> mapList = new List<T>();

                Command.CommandType = commandType;
                Command.CommandText = sql;

                ExecuteReader(sql, (reader) =>
                {
                    /*取出sql query內的欄位名稱*/
                    List<string> columnNamesInQuery = new List<string>();
                    foreach (DataRow dr in reader.GetSchemaTable().Rows) columnNamesInQuery.Add(Convert.ToString(dr[0]));

                    while (reader.Read())
                    {
                        T mapInstance;
                        mapInstance = CreateTObject<T>(mapType, reader, columnNamesInQuery);
                        mapList.Add(mapInstance);
                    }
                }, paramObj);

                return mapList;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        private static T CreateTObject<T>(Type mapType, IDataReader reader, List<string> columnNamesInQuery)
        {
            T mapInstance;
            if (mapType == typeof(string) || mapType.IsValueType == true)
            {
                mapInstance = (reader[0] is System.DBNull) ? default(T) : (T)reader[0];
            }
            else
            {
                mapInstance = (T)Activator.CreateInstance(mapType);
                foreach (PropertyInfo prop in mapType.GetProperties())
                {
                    object columnValue = null;

                    var colAttrs = prop.GetCustomAttributes(typeof(TColumnMapAttribute), false);
                    TColumnMapAttribute colMapAttr = new TColumnMapAttribute();
                    if (colAttrs.Length > 0) colMapAttr = colAttrs[0] as TColumnMapAttribute;

                    //依據是否有使用TcolumnMapAttribute來決定Datareader取資料時，要使用哪一個Column Name
                    string colName = (string.IsNullOrWhiteSpace(colMapAttr.Name) ? prop.Name : colMapAttr.Name);
                    bool colIsModelOnly = (colAttrs.Length == 0 ? false : colMapAttr.ModelOnly);

                    /*可以只select某幾個欄位，填入Model class中，即使Model class所設的欄位多於select欄位*/
                    if (!columnNamesInQuery.Contains(colName, StringComparer.OrdinalIgnoreCase)) continue;

                    //有使用TcolumnMapAttribute，且是ModelOnly，則不取資料
                    if (colIsModelOnly)
                        continue;

                    try
                    {
                        if (reader[colName] is System.DBNull)
                        {
                            columnValue = default(T); //如果是property是Nullable則回傳Null
                        }
                        else
                        {
                            //Check if Nullable type
                            if (Nullable.GetUnderlyingType(prop.PropertyType) != null)
                            {
                                columnValue = Convert.ChangeType(reader[colName], Nullable.GetUnderlyingType(prop.PropertyType));
                            }
                            else
                            {
                                columnValue = Convert.ChangeType(reader[colName], prop.PropertyType);
                            }
                        }
                        prop.SetValue(mapInstance, columnValue, null);
                    }
                    catch (Exception ex)
                    {
                        string message = string.Format("error when read column name:[\"{0}\"]", colName);
                        throw new TDataBaseException(message, ex);
                    }
                }
            }
            return mapInstance;
        }

        public dynamic ExecuteOne(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            dynamic obj = null;

            Command.CommandType = commandType;
            Command.CommandText = sql;

            ExecuteReader(sql, (reader) =>
            {
                while (reader.Read())
                {
                    var eo = new ExpandoObject();
                    var eoDict = (IDictionary<string, object>)eo;
                    for (int i = 0; i < reader.FieldCount; i++)
                    {//fill value
                        eoDict.Add(reader.GetName(i), (reader.IsDBNull(i) ? null : reader.GetValue(i)));
                    }
                    obj = eo;

                    break;
                }
            }, paramObj);

            return obj;
        }

        public DynamicList ExecuteList(string sql, object paramObj = null, CommandType commandType = CommandType.Text)
        {
            try
            {
                var list = new DynamicList();

                Command.CommandType = commandType;
                Command.CommandText = sql;

                ExecuteReader(sql, (reader) =>
                {
                    while (reader.Read())
                    {
                        var eo = new ExpandoObject();
                        var eoDict = (IDictionary<string, object>)eo;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {//fill value
                            eoDict.Add(reader.GetName(i), (reader.IsDBNull(i) ? null : reader.GetValue(i)));
                        }
                        dynamic obj = eo;

                        list.Add(obj);
                    }
                }, paramObj);

                return list;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        /// <summary>
        /// execute insert into [TABLE] base on paramObj name and value, return @@IDENTITY or 0 if no identity column
        /// </summary>
        /// <param name="targetTableName">table to insert</param>
        /// <param name="paramObj"></param>
        /// <returns>newly inserted @@IDENTITY, if no identity column in talbe will return 0</returns>
        public ExecuteInsertResult ExecuteInsert(string targetTableName, dynamic paramObj)
        {
            StringBuilder sb = new StringBuilder();

            ExecuteInsertResult insertResult = new ExecuteInsertResult();

            string sql = "";

            sb.AppendFormat("INSERT INTO {0} (", targetTableName);

            StringBuilder sbVals = new StringBuilder();
            sbVals.Append("VALUES(");

            if (paramObj is ExpandoObject)
            {
                ExpandoObject eo = paramObj;
                var kvpList = eo.ToList();
                foreach (var kvp in kvpList)
                {
                    if (this.tdbType == TDBType.ORACLE)
                        sb.AppendFormat("\"{0}\",", kvp.Key);
                    else
                        sb.AppendFormat("[{0}],", kvp.Key);

                    sbVals.AppendFormat("{0}{1},", parameterNamePrefix, kvp.Key);
                }
            }
            else
            {
                PropertyInfo[] propertyInfos = paramObj.GetType().GetProperties();
                foreach (PropertyInfo pi in propertyInfos)
                {
                    var colAttrs = pi.GetCustomAttributes(typeof(TColumnMapAttribute), false);
                    if (colAttrs.Length > 0)
                    {
                        var colMapAttr = colAttrs[0] as TColumnMapAttribute;
                        if (colMapAttr.IsIdentity)
                        {
                            continue;
                        }
                    }

                    if (this.tdbType == TDBType.ORACLE)
                        sb.AppendFormat("\"{0}\",", pi.Name);
                    else
                        sb.AppendFormat("[{0}],", pi.Name);

                    sbVals.AppendFormat("{0}{1},", parameterNamePrefix, pi.Name);
                }
            }

            string s1 = Regex.Replace(sb.ToString(), @",$", ")\n");
            string s2 = Regex.Replace(sbVals.ToString(), @",$", ")");
            sql = s1 + s2 + (tdbType == TDBType.SQLSERVER ? "\n;select SCOPE_IDENTITY(), @@ROWCOUNT;" : "");

            if (this.tdbType == TDBType.ORACLE)
            {
                insertResult.RowsAffected = this.ExecuteNonQuery(sql, (object)paramObj);
                insertResult.NewIdentity = 0;
            }
            else
            {
                this.ExecuteReader(sql, r =>
                {
                    while (r.Read())
                    {
                        insertResult.NewIdentity = Convert.ToInt32(r[0]);
                        insertResult.RowsAffected = Convert.ToInt32(r[1]);
                    }
                }, (object)paramObj);
            }

            return insertResult;
        }

        /// <summary>
        /// Bulk insert List to table
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sourceList"></param>
        /// <param name="destinationTableName"></param>
        /// <returns></returns>
        public int BulkInsert<T>(List<T> sourceList, string destinationTableName)
        {
            #region parameters checking
            if (sourceList == null)
                throw new TDataBaseException(string.Format("{0 is null}", sourceList));

            if (sourceList.Count == 0)
                return 0;

            DataTable dt = this.ExecuteDataTable(string.Format("select * from {0} where 1=0", destinationTableName));
            if (dt == null)
                throw new TDataBaseException(string.Format("cannot create DataTable from {0}", destinationTableName));
            #endregion

            Type mapType = typeof(T);
            foreach (var item in sourceList)
            {
                DataRow dr = dt.NewRow();

                foreach (PropertyInfo pi in mapType.GetProperties())
                {
                    #region IsIdentity column and timestamp column
                    var colAttrs = pi.GetCustomAttributes(typeof(TColumnMapAttribute), false);
                    TColumnMapAttribute colMapAttr = new TColumnMapAttribute();
                    if (colAttrs.Length > 0) colMapAttr = colAttrs[0] as TColumnMapAttribute;

                    bool colIsIdentity = (colAttrs.Length == 0 ? false : colMapAttr.IsIdentity);
                    if (colIsIdentity) continue;

                    bool colIsTimestamp = (colAttrs.Length == 0 ? false : colMapAttr.IsTimestamp);
                    if (colIsTimestamp) continue;
                    #endregion

                    object rowValue;
                    var underlyinType = Nullable.GetUnderlyingType(pi.PropertyType);
                    if (underlyinType != null)
                        rowValue = (pi.GetValue(item, null) == null ? DBNull.Value : pi.GetValue(item, null)); //is nullable type
                    else
                        rowValue = pi.GetValue(item, null);

                    dr[pi.Name] = rowValue;
                }

                dt.Rows.Add(dr);
            }

            if (dt.Rows.Count > 0)
            {
                this.BulkCopyToSQL(dt, destinationTableName);
            }

            return dt.Rows.Count;
        }

        /// <summary>
        /// <para>execute UPDATE [TABLE] set COL = COL_VALUE where [IDENTITY_COL] = [IDENTITY_COL_VALUE]</para>
        /// <para>依據objToUpdate的值，更新TABLE的資料，TABLE必須含有IDENTITY的值</para>
        /// </summary>
        /// <param name="targetTableName"></param>
        /// <param name="objToUpdate">object mapping to TABLE</param>
        /// <returns></returns>
        public int ExecuteUpdate(string targetTableName, object objToUpdate)
        {
            if (objToUpdate == null) return 0;

            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();

            sb.AppendFormat("UPDATE {0} SET ", targetTableName);

            string identityColName = "";

            PropertyInfo[] propertyInfos = objToUpdate.GetType().GetProperties();
            foreach (PropertyInfo pi in propertyInfos)
            {
                var colAttrs = pi.GetCustomAttributes(typeof(TColumnMapAttribute), false);
                if (colAttrs.Length > 0)
                {
                    var colMapAttr = colAttrs[0] as TColumnMapAttribute;
                    if (colMapAttr.IsIdentity)
                    {
                        identityColName = pi.Name;
                        continue;
                    }
                }

                sb.AppendFormat("[{0}]={1}{0},", pi.Name, parameterNamePrefix);
            }

            if (!string.IsNullOrWhiteSpace(identityColName))
            {
                sb2.AppendFormat("WHERE [{0}]={1}{0}", identityColName, parameterNamePrefix);
            }

            string s1 = Regex.Replace(sb.ToString(), @",$", "\n");
            string s2 = sb2.ToString();
            string sql = s1 + s2;

            CreateDbParameters(objToUpdate, clearParameters: true);

            int affected;
            try
            {
                Command.CommandType = CommandType.Text;
                Command.CommandText = sql;

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                affected = Command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return affected;
        }

        /// <summary>
        /// execute UPDATE [TABLE] base on params
        /// </summary>
        /// <param name="targetTableName">table to update</param>
        /// <param name="paramObjSet">for SET parameter</param>
        /// <param name="paramObjWhere">for WHERE parameter</param>
        /// <returns>updated records count</returns>
        public int ExecuteUpdate(string targetTableName, dynamic paramObjSet, dynamic paramObjWhere)
        {
            if (paramObjSet == null) return 0;

            StringBuilder sb = new StringBuilder();
            StringBuilder sb2 = new StringBuilder();

            sb.AppendFormat("UPDATE {0} SET ", targetTableName);

            if (paramObjSet is ExpandoObject)
            {
                ExpandoObject eo = paramObjSet;
                var kvpList = eo.ToList();
                foreach (var kvp in kvpList)
                {
                    sb.AppendFormat("[{0}]={1}{0},", kvp.Key, parameterNamePrefix);
                }
            }
            else
            {
                PropertyInfo[] propertyInfos = paramObjSet.GetType().GetProperties();
                foreach (PropertyInfo pi in propertyInfos)
                {
                    sb.AppendFormat("[{0}]={1}{0},", pi.Name, parameterNamePrefix);
                }
            }

            if (paramObjWhere != null)
            {
                sb2.AppendFormat("WHERE ");

                if (paramObjWhere is ExpandoObject)
                {
                    ExpandoObject eo = paramObjSet;
                    var kvpList = eo.ToList();
                    foreach (var kvp in kvpList)
                    {
                        sb2.AppendFormat("[{0}]={1}{0} and ", kvp.Key, parameterNamePrefix);
                    }
                }
                else
                {
                    PropertyInfo[] propertyInfos2 = paramObjWhere.GetType().GetProperties();
                    foreach (PropertyInfo pi in propertyInfos2)
                    {
                        sb2.AppendFormat("[{0}]={1}{0} and ", pi.Name, parameterNamePrefix);
                    }
                }
            }

            string s1 = Regex.Replace(sb.ToString(), @",$", "\n");
            string s2 = Regex.Replace(sb2.ToString(), @"and $", "");
            string sql = s1 + s2;

            CreateDbParameters(paramObjSet, clearParameters: true);
            CreateDbParameters(paramObjWhere, clearParameters: false);

            int affected;
            try
            {
                Command.CommandType = CommandType.Text;
                Command.CommandText = sql;

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                affected = Command.ExecuteNonQuery();
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }

            return affected;
        }

        public ExecuteStoredProcedureResult ExecuteStoredProcedure(string sql, object paramIn, object paramOut)
        {
            try
            {
                ExecuteStoredProcedureResult result = new ExecuteStoredProcedureResult();

                Command.CommandType = CommandType.StoredProcedure;
                Command.CommandText = sql;
                if (paramOut != null)
                {
                    CreateDbParameters(paramOut, ParameterDirection.Output, true);
                }

                if (paramIn != null)
                {
                    CreateDbParameters(paramIn, ParameterDirection.Input, false);
                }

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                using (IDataReader reader = Command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var _eo = new ExpandoObject();
                        var _eoDict = (IDictionary<string, object>)_eo;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {//fill value
                            _eoDict.Add(reader.GetName(i), reader.GetValue(i));
                        }
                        dynamic _obj = _eo;

                        result.DynamicListResult.Add(_obj);
                    }
                }

                //將out parameter的值
                var eo = new ExpandoObject();
                var eoDict = (IDictionary<string, object>)eo;
                foreach (DbParameter pOut in Command.Parameters)
                {
                    if (pOut.Direction == ParameterDirection.Output)
                    {
                        eoDict.Add(pOut.ParameterName, pOut.Value);
                    }
                }
                dynamic obj = eo;
                result.ReturnedOutParameter = obj;

                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public ExecuteStoredProcedureResult ExecuteStoredProcedure(string sql, TDbParameter[] tDbParams)
        {
            try
            {
                ExecuteStoredProcedureResult result = new ExecuteStoredProcedureResult();

                Command.CommandType = CommandType.StoredProcedure;
                Command.CommandText = sql;

                Command.Parameters.Clear();
                //build DbParameter
                foreach (var p in tDbParams)
                {
                    DbParameter param = Command.CreateParameter();
                    param.DbType = p.DbType;
                    param.ParameterName = p.ParameterName;
                    param.Size = p.Size;
                    param.Direction = p.Direction;
                    param.Value = p.Value;

                    Command.Parameters.Add(param);
                }

                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();
                using (IDataReader reader = Command.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        var _eo = new ExpandoObject();
                        var _eoDict = (IDictionary<string, object>)_eo;
                        for (int i = 0; i < reader.FieldCount; i++)
                        {//fill value
                            _eoDict.Add(reader.GetName(i), reader.GetValue(i));
                        }
                        dynamic _obj = _eo;

                        result.DynamicListResult.Add(_obj);
                    }
                }

                //將out parameter的值
                var eo = new ExpandoObject();
                var eoDict = (IDictionary<string, object>)eo;
                foreach (DbParameter pOut in Command.Parameters)
                {
                    if (pOut.Direction == ParameterDirection.Output)
                    {
                        eoDict.Add(pOut.ParameterName, pOut.Value);
                    }
                }
                dynamic obj = eo;
                result.ReturnedOutParameter = obj;

                return result;
            }
            catch
            {
                throw;
            }
            finally
            {
                if (Command.Transaction == null)
                    Connection.Close();
            }
        }

        public bool DoTransaction(Action<TDataBase> block, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            bool result = false;

            DbTransaction trans = null;
            try
            {
                if (Connection.State == ConnectionState.Closed)
                    Connection.Open();

                trans = Connection.BeginTransaction(isolationLevel);
                Command.Transaction = trans;
                block(this);
                trans.Commit();

                result = true;
            }
            catch
            {
                trans.Rollback();
                throw;
            }
            finally
            {
                Command.Transaction = null;
                Connection.Close();
            }

            return result;
        }

        public void SqlBatchDelete(string cmdText)
        {
            Regex rx = new Regex(@"\bdelete\b\s+", RegexOptions.Compiled | RegexOptions.IgnoreCase);

            string sqlDel = @"DECLARE @BatchSize INT
SET @BatchSize = 100000

WHILE @BatchSize <> 0
BEGIN
    DELETE TOP (@BatchSize)
    {{DELETESQL}}
    SET @BatchSize = @@rowcount
END";
            sqlDel = sqlDel.Replace(@"{{DELETESQL}}", rx.Replace(cmdText, ""));
            this.ExecuteNonQuery(sqlDel);
        }

        public void BulkCopyToSQL(DataTable SourceDataTable, string DestinationTableName)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(Connection.ConnectionString);

            bcp.BulkCopyTimeout = 1500;
            bcp.BatchSize = 500000;
            bcp.DestinationTableName = DestinationTableName;
            bcp.WriteToServer(SourceDataTable);
        }

        public void BulkCopyToSQLWithColumnMapping(DataTable SourceDataTable, string DestinationTableName)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(Connection.ConnectionString, SqlBulkCopyOptions.KeepIdentity);

            //mapping column, if Dstn table has identity column, do not select into DataTable
            foreach (DataColumn dc in SourceDataTable.Columns)
            {
                bcp.ColumnMappings.Add(dc.Caption, dc.Caption);
            }

            bcp.BulkCopyTimeout = 1500;
            bcp.BatchSize = 500000;
            bcp.DestinationTableName = DestinationTableName;
            bcp.WriteToServer(SourceDataTable);
        }

        public void BulkCopyToSQL(IDataReader reader, string DestinationTableName)
        {
            SqlBulkCopy bcp = new SqlBulkCopy(Connection.ConnectionString);

            bcp.BulkCopyTimeout = 1500;
            bcp.BatchSize = 500000;
            bcp.DestinationTableName = DestinationTableName;
            bcp.WriteToServer(reader);
        }
        #endregion

        #region DbParameter

        private void CreateDbParameters(object paramObj, ParameterDirection parameterDirection = ParameterDirection.Input, bool clearParameters = true)
        {
            if (clearParameters)
                Command.Parameters.Clear();

            if (paramObj == null) return;

            if (paramObj is ExpandoObject)
            {
                ExpandoObject eo = paramObj as ExpandoObject;
                var kvpList = eo.ToList();
                foreach (var kvp in kvpList)
                {
                    try
                    {
                        delegateCreateDbParameters(kvp.Key, kvp.Value, kvp.Value.GetType(), parameterDirection);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Error ocurred while create parameters from dynamic paramObj", ex);
                    }
                }
            }
            else
            {
                PropertyInfo[] propertyInfos = paramObj.GetType().GetProperties();
                foreach (PropertyInfo pi in propertyInfos)
                {
                    try
                    {
                        delegateCreateDbParameters(pi.Name, pi.GetValue(paramObj, null), pi.PropertyType, parameterDirection);
                    }
                    catch (Exception ex)
                    {
                        throw new Exception("Error ocurred while create parameters from dynamic paramObj", ex);
                    }
                }
            }
        }

        private void delegateCreateDbParameters(string parameterName, object parameterValue, Type parameterType, ParameterDirection parameterDirection)
        {
            var param = Command.CreateParameter();
            param.ParameterName = (this.Factory is SqlClientFactory ? "@" : "") + parameterName;
            param.Direction = parameterDirection;

            var underlyinType = Nullable.GetUnderlyingType(parameterType);
            if (underlyinType != null)
            {//is nullable type
                param.DbType = DBTypeMap[underlyinType];
                param.Value = (parameterValue == null ? DBNull.Value : parameterValue); //allow null的column，填入null
            }
            else
            {
                param.DbType = DBTypeMap[parameterType];
                param.Value = parameterValue;

                if (parameterType == typeof(string) && param.Value == null)
                {
                    param.Value = DBNull.Value;
                }
            }

            Command.Parameters.Add(param);
        }

        #region DBTypeMap
        private static Dictionary<Type, DbType> DBTypeMap = new Dictionary<Type, DbType>
        {
            { typeof(Boolean), DbType.Boolean },
            { typeof(Byte), DbType.Byte },
            { typeof(Char), DbType.String },
            { typeof(DateTime), DbType.DateTime },
            { typeof(Decimal), DbType.Decimal },
            { typeof(Double), DbType.Double },
            { typeof(Int16), DbType.Int16 },
            { typeof(Int32), DbType.Int32 },
            { typeof(Int64), DbType.Int64 },
            { typeof(SByte), DbType.SByte },
            { typeof(Single), DbType.Single},
            { typeof(String), DbType.String },
            { typeof(TimeSpan), DbType.Time },
            { typeof(UInt16), DbType.UInt16},
            { typeof(UInt32), DbType.UInt32 },
            { typeof(UInt64), DbType.UInt64 },
            { typeof(Guid), DbType.Guid },
            { typeof(Byte[]), DbType.Binary },
            { typeof(DateTimeOffset), DbType.DateTimeOffset },
            { typeof(Object), DbType.Object },
            { typeof(DBNull), DbType.String}
        };
        #endregion

        #endregion

        #region ConnectionString

        public string AddConnectionStringValue(string key, string value)
        {
            string newConnectionString = string.Empty;
            if (this.Connection.ConnectionString.EndsWith(";"))
            {
                newConnectionString =
                    this.Connection.ConnectionString + key + "=" + value + ";";
            }
            else
            {
                newConnectionString =
                    this.Connection.ConnectionString + ";" + key + "=" + value + ";";
            }

            return newConnectionString;
        }

        public string ChangeConnectionStringValue(string key, string newValue)
        {
            string newConnectionString = string.Empty;
            Regex rx = new Regex(@"(?<=\b" + key + @"\b\s*=\s*)([^;]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            newConnectionString = rx.Replace(this.Connection.ConnectionString, newValue);
            return newConnectionString;
        }

        public string GetConnectionStringValue(string key)
        {
            Regex rx = new Regex(@"\b" + key + @"\b\s*=\s*([^;]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            MatchCollection matches = rx.Matches(this.Connection.ConnectionString);
            string value = string.Empty;
            if (matches.Count == 1)
                value = matches[0].Groups[1].Value;
            else
                throw new Exception("Cannot find <" + key + "> in connection string");

            return value;
        }
        #endregion

        #region SqlServer BCP
        public void SqlBcpIn(string bcpExeFullPath, string inputFileName, string DestinationTableName, bool pauseWhenCompleted)
        {
            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("{0} in \"{1}\" ", DestinationTableName, inputFileName);
                sb.AppendFormat("-c -S\"{0}\" -U\"{1}\" -P\"{2}\" -b10000 -h\"TABLOCK\"", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

        }

        public string SqlBcpTableOut(string bcpExeFullPath, string tableName, bool pauseWhenCompleted)
        {
            string output = tableName + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".dat";
            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("{0} out {1} ", tableName, output);
                sb.AppendFormat("-c -S{0} -U{1} -P{2}", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

            return output;
        }

        public string SqlBcpQueryOut(string bcpExeFullPath, string sql, bool pauseWhenCompleted)
        {
            sql = sql.Replace(Environment.NewLine, " ");
            string output = string.Empty;

            Regex rx = new Regex(@"from\s([\w\.]+)", RegexOptions.Compiled | RegexOptions.IgnoreCase);
            MatchCollection matches = rx.Matches(sql);

            if (matches.Count == 1)
            {
                output = matches[0].Groups[1].Value + "_" + DateTime.Now.ToString("yyyyMMdd_hhmmss") + ".dat";
            }
            else
            {
                output = "_bcpoutData_" + Path.GetRandomFileName() + ".dat";
            }

            string server = GetConnectionStringValue("Data Source");
            string uid = GetConnectionStringValue("User ID");
            string pwd = GetConnectionStringValue("Password");

            string bcpbatch = "_tmpbatch_" + Path.GetRandomFileName() + ".bat";
            using (TextWriter tw2 = new StreamWriter(bcpbatch))
            {
                StringBuilder sb = new StringBuilder();
                sb.AppendLine("@echo off");
                sb.AppendFormat("\"{0}\" ", bcpExeFullPath);
                sb.AppendFormat("\"{0}\" queryout {1} ", sql, output);
                sb.AppendFormat("-c -S{0} -U{1} -P{2}", server, uid, pwd);
                sb.AppendLine();
                if (pauseWhenCompleted) sb.AppendLine("pause");
                tw2.WriteLine(sb.ToString());
            }

            var p = Process.Start("cmd", "/c " + bcpbatch);
            p.WaitForExit();
            File.Delete(bcpbatch);

            return output;
        }
        #endregion
    }
}
